package com.example.new4.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.new4.entity.Course;

import java.util.ArrayList;
import java.util.List;

public class DBHelper2 extends SQLiteOpenHelper {

   private static final String TAG = "DBHelper2";
   private static final String DATABASE_NAME = "db_course_manager";// 数据库名
   private static final int DATABASE_VERSION = 1;// 数据库版本号
   private static final String TABLE_NAME_STUDENT = "course"; //学生表

   //private static DBHelper mHelper = null;
   public DBHelper2(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
   }

   //单例模式
   /*public static DBHelper getInstance(Context context){
      if(mHelper == null){
         mHelper = new DBHelper(context);
      }
      return mHelper;
   }*/

   //数据库创建时被调用
   @Override
   public void onCreate(SQLiteDatabase db) {
      initTable(db);
      initData(db);
   }

   //数据库升级
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

   }

   /**
    * 初始化数据表
    *
    * @param db 数据库
    */
   private void initTable(SQLiteDatabase db) {
      // 创建学生表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_STUDENT + " (" +
              "_id integer primary key autoincrement," +
              "course_name text," +
              "course_code text)");
   }

   /**
    * 初始化数据库内部数据(仅在第一次安装时执行)
    *
    * @param db 数据库
    */
   private void initData(SQLiteDatabase db) {
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (course_name , course_code) " +
              "values" +
              "('Android开发', '0001')");
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (course_name , course_code) " +
              "values" +
              "('前端开发', '0002')");
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (course_name , course_code) " +
              "values" +
              "('J2EE框架', '0003')");
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (course_name , course_code) " +
              "values" +
              "('Vue框架', '0004')");
   }

   public void insertCourse(Course course) {
      SQLiteDatabase db = getWritableDatabase();
      db.execSQL("insert into " + TABLE_NAME_STUDENT +
              " (course_name , course_code) " +
              "values" +
              "('" + course.getCourse_name() + "','" + course.getCourse_code()+ "')");
   }

   public void insertStudent2(Course course) {
      //获取可写数据库
      SQLiteDatabase db = getWritableDatabase();

      //创建ContentValues以Key-Value的形式封装数据
      ContentValues values = new ContentValues();
      values.put("course_name", course.getCourse_name());
      values.put("course_code", course.getCourse_code());

      //执行insert方法，插入数据
      long newRowId = db.insert(TABLE_NAME_STUDENT, null, values);
      Log.d(TAG, "insertCourse2 newRowId = " + newRowId);
   }

   public void deleteCourseByName(String name) {
      if (name != null) {
         SQLiteDatabase db = getReadableDatabase();
         int changedRowCount = db.delete(TABLE_NAME_STUDENT, "course_name = ?", new String[]{name});
         Log.d(TAG, "deleteCourseByName changedRowCount = " + changedRowCount);
      }
   }

   public void updateCourseNameById(String name, int id) {
      if (name != null && id >= 0) {
         SQLiteDatabase db = getReadableDatabase();
         ContentValues values = new ContentValues();
         values.put("course_name", name);
         int changedRowCount = db.update(TABLE_NAME_STUDENT, values, "_id = ?", new String[] {String.valueOf(id)});
         Log.d(TAG, "updateCourseNameById changedRowCount = " + changedRowCount);
      }
   }

   @SuppressLint("Range")
   public List<Course> selectAllCourse() {
      List<Course> list = new ArrayList<>();
      SQLiteDatabase db = getReadableDatabase();

      Cursor cursor = db.query(
              TABLE_NAME_STUDENT,     // 表名
              null,                   // 要查询的列数据
              null,                   // 条件语句
              null,                   // 条件语句的值
              null,                   // group by
              null,                   // having
              null                    // 排序语句
      );
      if (cursor != null && cursor.getCount() > 0) {
         while (cursor.moveToNext()) {
            Course course = new Course();
            course.setCourse_id(cursor.getInt(cursor.getColumnIndex("_id")));
            course.setCourse_name(cursor.getString(cursor.getColumnIndex("course_name")));
            course.setCourse_code(cursor.getString(cursor.getColumnIndex("course_code")));
            list.add(course);
         }
      }
      Log.d(TAG, "selectAllCourse = " + list.size());
      return list;
   }
}
